﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace LiteQuery.SqlFuncHelper
{
    class AsposeHelper
    {
        static string logTableName = "_logs_";

        /// <summary>
        /// 创建日志表
        /// </summary>
        /// <param name="dbpath"></param>
        /// <returns></returns>
        public static bool CreateLogDB(string dbpath)
        {
            // 表名
            SQLiteTable tb = new SQLiteTable(logTableName);
            tb.Columns.Add(new SQLiteColumn("id", ColType.Integer,true,true,true,"0"));
            // 文件名
            tb.Columns.Add(new SQLiteColumn("file_name", ColType.Text));
            // 文件全路径
            tb.Columns.Add(new SQLiteColumn("file_full_path", ColType.Text));
            // 文件导入的数据表名
            tb.Columns.Add(new SQLiteColumn("table_name", ColType.Text));
            // 导入状态
            tb.Columns.Add(new SQLiteColumn("status", ColType.Text));
            // 导入的时间戳
            tb.Columns.Add(new SQLiteColumn("add_time", ColType.Timestamp));


            // 创建表
            using (SQLiteConnection conn = new SQLiteConnection(dbpath))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    SQLiteHelper sh = new SQLiteHelper(cmd);
                    sh.CreateTable(tb);
                    conn.Close();
                }
            }
            return true;
        }

        private static Tuple<long, Exception> InsertIntoLog(string dbpath, string tablename, string path, string status)
        {
            try
            {
                var ret = -1L;

                var data = SetValue(new List<Tuple<string, object>>() {
                    new Tuple<string, object>("file_name",path),
                    new Tuple<string, object>("file_full_path",path),
                    new Tuple<string, object>("table_name",tablename),
                    new Tuple<string, object>("status",status),
                    new Tuple<string, object>("add_time",Common.DateHelper.DateToTimestamp(DateTime.Now)),
                });


                using (SQLiteConnection conn = new SQLiteConnection(dbpath))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        sh.Insert(logTableName, data);
                        ret = sh.LastInsertRowId();
                        conn.Close();
                    }
                }
                return new Tuple<long, Exception>(ret , null);
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message, ex);
                return new Tuple<long, Exception>(-1, ex);
            }
        }

        private static Tuple<bool, Exception> UpdateLog(string dbpath, string id, string status)
        {
            try
            {
                var ret = -1L;

                var data = SetValue(new List<Tuple<string, object>>() {
                    new Tuple<string, object>("status",status),
                });


                using (SQLiteConnection conn = new SQLiteConnection(dbpath))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        sh.Update(logTableName, data, "id", id);
                        conn.Close();
                    }
                }
                return new Tuple<bool, Exception>(true, null);
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message, ex);
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        private static Dictionary<string, object> SetValue(List<Tuple<string, object>> data)
        {
            var d = new Dictionary<string, object>();
            foreach (Tuple<string,object> item in data)
            {
                d.Add(item.Item1,item.Item2);
            }
            return d;
        }

        /// <summary>
        /// 通过Excel模板创建数据表, 根据Excel的sheet名称来命名表名, 默认都是text类型
        /// </summary>
        /// <param name="dbpath">数据库路径</param>
        /// <param name="tablename">数据表名</tablename>
        /// <param name="temppath">模板路径</param>
        /// <returns></returns>
        public static Tuple<bool, Exception> CreateDBFromTemplate(string dbpath,string tablename, string temppath)
        {
            try
            {
                Workbook workbook = new Workbook(temppath);
                Cells cells = workbook.Worksheets[0].Cells;

                // 表名
                SQLiteTable tb = new SQLiteTable(tablename);

                for (int i = 0; i < cells.MaxColumn + 1; i++)
                {
                    string s = cells[0, i].StringValue.Trim();
                    tb.Columns.Add(new SQLiteColumn(s, GetColType(cells[1, i].Type)));
                }

                // 创建表
                using (SQLiteConnection conn = new SQLiteConnection(dbpath))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);

                        sh.DropTable(tablename);
                        sh.CreateTable(tb);

                        conn.Close();
                    }
                }
                return new Tuple<bool, Exception>(true, null);
            }
            catch(Exception ex) {
                LogHelper.Error(ex.Message, ex);
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        /// <summary>
        /// 将Excel的第一个sheet数据导入到指定数据表中（不创建数据表）
        /// </summary>
        /// <param name="dbpath">数据表名</param>
        /// <param name="tablename">数据表名</param>
        /// <param name="path">Excel路径</param>
        /// <returns></returns>
        public static Tuple<bool, Exception> ReadExcelSheetOneToDataTable(string dbpath, string tablename, string path)
        {
            Workbook workbook = new Workbook(path);
            Cells cells = workbook.Worksheets[0].Cells;

            var dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxDataColumn+1, false);

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(dbpath))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);

                        var ret = sh.Insert(tablename, dt);
                        if (ret.Item1)
                            return new Tuple<bool, Exception>(true, null);
                        else
                        {
                            LogHelper.Error(ret.Item2.Message, ret.Item2);
                            return new Tuple<bool, Exception>(false, ret.Item2);
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                LogHelper.Error(ex.Message, ex);
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        /// <summary>
        /// 将Excel的第一个sheet数据导入到指定数据表中（自动创建数据表）
        /// </summary>
        /// <param name="dbpath"></param>
        /// <param name="tablename"></param>
        /// <param name="path"></param>
        /// <param name="droptable"></param>
        /// <returns></returns>
        public static Tuple<bool, Exception> CreateAndInsert(string dbpath, string tablename, string path, bool droptable=false)
        {
            Workbook workbook = new Workbook(path);
            Cells cells = workbook.Worksheets[0].Cells;

            // 表名
            SQLiteTable tb = new SQLiteTable(tablename);

            for (int i = 0; i < cells.MaxColumn + 1; i++)
            {
                string s = cells[0, i].StringValue.Trim();
                tb.Columns.Add(new SQLiteColumn(s, GetColType(cells[1, i].Type)));
            }


            var dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxDataColumn+1, false);

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(dbpath))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        SQLiteHelper sh = new SQLiteHelper(cmd);
                        if(droptable)
                            sh.DropTable(tablename);
                        sh.CreateTable(tb);
                        var ret = sh.Insert(tablename, dt);
                        if (ret.Item1)
                        {
                            InsertIntoLog(dbpath, tablename, path, "success");
                            return new Tuple<bool, Exception>(true, null);
                        }
                        else
                        {
                            LogHelper.Error(ret.Item2.Message, ret.Item2);
                            return new Tuple<bool, Exception>(false, ret.Item2);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message, ex);
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        /// <summary>
        /// 将一批Excel的第一个sheet数据导入到指定数据表中（自动创建数据表）
        /// </summary>
        /// <param name="dbpath"></param>
        /// <param name="tablename"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static Tuple<bool, Exception> CreateAndInsert(string dbpath, string tablename, string[] path)
        {
            // 以第一个文件为模板创建数据库
            if (!CreateDBFromTemplate(dbpath, tablename, path[0]).Item1)
                return new Tuple<bool, Exception>(false, new Exception($"数据表[{tablename}]创建失败"));

            foreach (string item in path)
            {
                
                var ret = ReadExcelSheetOneToDataTable(dbpath, tablename, item);
                if (!ret.Item1)
                    return new Tuple<bool, Exception>(false,ret.Item2);
                else
                    InsertIntoLog(dbpath, tablename, item, "success");
            }
            return new Tuple<bool, Exception>(true, null);
        }

        /// <summary>
        /// 将datatable导出到excel
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static Tuple<bool, Exception> ExportDataTableToExcel(DataTable data, string path)
        {
            try
            {
                Workbook workbook = new Workbook();
                Worksheet sheet = workbook.Worksheets[0];
                Cells cells = sheet.Cells;

                int Colnum = data.Columns.Count;//表格列数 
                int Rownum = data.Rows.Count;//表格行数 

                //生成行 列名行 
                for (int i = 0; i < Colnum; i++)
                {
                    cells[0, i].PutValue(data.Columns[i].ColumnName);
                }

                //生成数据行 
                for (int i = 0; i < Rownum; i++)
                {
                    for (int k = 0; k < Colnum; k++)
                    {
                        if (data.Columns[k].DataType == typeof(Int64))
                            cells[1 + i, k].PutValue(LiteQuery.Common.DateHelper.TimestampToDate((long)data.Rows[i][k]).ToString("yyyy-MM-dd HH:mm:ss"));
                        else
                            cells[1 + i, k].PutValue(data.Rows[i][k].ToString());
                    }
                }

                workbook.Save(path);
                return new Tuple<bool, Exception>(true, null);
            }
            catch (Exception ex){
                LogHelper.Error(ex.Message, ex);
                return new Tuple<bool, Exception>(false, ex);
            }
        }

        private static System.Data.SQLite.ColType GetColType(CellValueType type)
        {
            switch (type)
            {
                case CellValueType.IsBool:
                    return ColType.Text;
                case CellValueType.IsDateTime:
                    return ColType.Timestamp;
                case CellValueType.IsString:
                    return ColType.Text;
                case CellValueType.IsNumeric:
                    return ColType.Decimal;
                default: return ColType.Text;
            }
        }
    }
}
